package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import data.dao.*;
import data.dto.*;
import data.searchdto.*;

public class NatureOfLeaveDAOImpl implements NatureOfLeaveDAO {

	@Override
	public ArrayList<NatureOfLeaveDTO> findNatureOfLeavebyName(
			NatureOfLeaveSearchDTO natureofleavesearchdto)
			throws LeaveDAOException {
		
		ArrayList<NatureOfLeaveDTO> searchResults = null;
		try {
			searchResults = searchMatching(natureofleavesearchdto);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return searchResults;
	}

	@Override
	public ArrayList<NatureOfLeaveDTO> findAllNatureOfLeave()
			throws LeaveDAOException, SQLException, java.sql.SQLException {
		String sql = "SELECT * FROM natureofleave WHERE isactive = 1 ORDER BY id ASC ";
		ArrayList<NatureOfLeaveDTO> searchResults = listQuery(util.Database
				.getInstance().getConnection().prepareStatement(sql));

		return searchResults;
	}

	@Override
	public void insertLeaveDetailType(NatureOfLeaveDTO LeaveDetail)
	throws LeaveDAOException, java.sql.SQLException {
		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO natureofleave (name, "
					+ "status,isactive) VALUES (?, ?,1) ";
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			//stmt.setInt(1, LeaveDetail.getId());
			stmt.setString(1, LeaveDetail.getName());
			//stmt.setInt(2, util.CurrentUser.getInstance().getUserId());
			//stmt.setTimestamp(3, util.CurrentDate.getCurrentTimeStamp());
			//stmt.setInt(4, util.CurrentUser.getInstance().getUserId());
			//stmt.setTimestamp(5, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(6, LeaveDetail.getStatus());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void updateLeaveDetailType(NatureOfLeaveDTO LeaveDetail)
			throws LeaveDAOException, java.sql.SQLException {

		String sql = "UPDATE natureofleave SET name = ?, "
			+ "status = ? WHERE (id = ? ) ";
	PreparedStatement stmt = null;

	try {
		stmt = util.Database.getInstance().getConnection()
				.prepareStatement(sql);
		stmt.setString(1, LeaveDetail.getName());
		//stmt.setInt(2, util.CurrentUser.getInstance().getUserId());
		//stmt.setTimestamp(3, util.CurrentDate.getCurrentTimeStamp());
		stmt.setInt(2, LeaveDetail.getStatus());

		stmt.setInt(3, LeaveDetail.getId());

		int rowcount = util.Database.getInstance().executeUpdate(stmt);
		if (rowcount == 0) {
			// System.out.println("Object could not be saved! (PrimaryKey not found)");
			throw new NotFoundException(
					"Object could not be saved! (PrimaryKey not found)");
		}
		if (rowcount > 1) {
			// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
			throw new SQLException(
					"PrimaryKey Error when updating DB! (Many objects were affected!)");
		}
	} catch (NotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		if (stmt != null)
			stmt.close();
	}
	}

	@Override
	public void deleteLeaveDetailType(NatureOfLeaveDTO LeaveDetail)
			throws LeaveDAOException, java.sql.SQLException {
		String sql = "UPDATE natureofleave SET isactive = 0 WHERE (id = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = util.Database.getInstance().getConnection().prepareStatement(sql);
            stmt.setInt(1, LeaveDetail.getId()); 

            int rowcount = util.Database.getInstance().executeUpdate(stmt);
            if (rowcount == 0) {
                 //System.out.println("Object could not be deleted (PrimaryKey not found)");
                 throw new NotFoundException("Object could not be deleted! (PrimaryKey not found)");
            }
            if (rowcount > 1) {
                 //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                 throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
            }
        } catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
            if (stmt != null)
                stmt.close();
        }

	}

	public ArrayList<NatureOfLeaveDTO> searchMatching(
			NatureOfLeaveSearchDTO valueObject) throws SQLException,
			java.sql.SQLException {

		ArrayList<NatureOfLeaveDTO> searchResults;

		boolean first = true;
		StringBuffer sql = new StringBuffer(
				"SELECT * FROM natureofleave WHERE isactive = 1 AND 1=1 ");

		if (valueObject.getId() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND id = ").append(valueObject.getId()).append(" ");
		}

		if (valueObject.getName() != "") {
			if (first) {
				first = false;
			}
			sql.append("AND name LIKE '").append(valueObject.getName())
					.append("%' ");
		}

		sql.append("ORDER BY id ASC ");

		// Prevent accidential full table results.
		// Use loadAll if all rows must be returned.
		if (first)
			searchResults = new ArrayList<NatureOfLeaveDTO>();
		else
			searchResults = listQuery(util.Database.getInstance()
					.getConnection().prepareStatement(sql.toString()));

		return searchResults;
	}

	protected ArrayList<NatureOfLeaveDTO> listQuery(PreparedStatement stmt)
			throws SQLException {

		ArrayList<NatureOfLeaveDTO> searchResults = new ArrayList<NatureOfLeaveDTO>();
		ResultSet result = null;
		try {
			result = util.Database.getInstance().executeQuery(stmt);

			while (result.next()) {
				NatureOfLeaveDTO temp = new NatureOfLeaveDTO();

				temp.setId(result.getInt("id"));
				temp.setName(result.getString("name"));
				//temp.setCreatedBy(result.getInt("createdBy"));
				//temp.setCreatedOn(result.getTimestamp("createdOn"));
				//temp.setModifiedBy(result.getInt("modifiedBy"));
				//temp.setModifiedOn(result.getTimestamp("modifiedOn"));
				temp.setStatus(result.getShort("status"));

				searchResults.add(temp);
			}

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (result != null)
				try {
					result.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return searchResults;
	}

}
